/*
	exec sp_assembly_release_report_proc '0','7','0','0','0','0','SUMMARY','0','0'
	exec sp_assembly_release_report_proc '0','0','0','0','06/01/2010','','DETAIL','XLREPORT','0'
*/
ALTER proc sp_assembly_release_report_proc

@PROCESSSTEP_IDS	VARCHAR(200),
@VENDORID		VARCHAR(50),
@STATUS			VARCHAR(50),
@CATEGORY		VARCHAR(50),
@FROMDATE		VARCHAR(50),
@TODATE			VARCHAR(50),
@ISDETAIL		VARCHAR(50),
@REPORTTYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN

DECLARE @SQLSTRING VARCHAR(4000)

DECLARE @RELEASE_CURSOR	CURSOR
DECLARE @WAFER_CURSOR	CURSOR
DECLARE @LOT_CURSOR	CURSOR

DECLARE @WAFER_IDS_TABLE TABLE ( LOT_ID INT, WAFER_ID INT )

DECLARE @RELEASE_ID INT
DECLARE @RELEASE_DATE DATETIME
DECLARE @WAFER_ID  VARCHAR(10)
DECLARE @WAFER_IDS VARCHAR(200)
DECLARE @LOT_ID INT
DECLARE @LOT_NUMBER VARCHAR(50)
DECLARE @FAB_LOT_NUMBER VARCHAR(50)
DECLARE @LOT_NUMBERS VARCHAR(200)
DECLARE @FAB_LOT_NUMBERS VARCHAR(200)
DECLARE @DB_INSTRUCTIONS VARCHAR(500)
DECLARE @LOTWAFER_IDS	VARCHAR(200)
DECLARE @@WW_NO VARCHAR(10)
CREATE TABLE #RELEASE_TABLE 
			([ASSM REL ID]int,[ASSM RELEASE NO] VARCHAR(50), [RELEASE DATE] DATETIME, [PO NO] VARCHAR(50),
			 [FROM VENDOR] VARCHAR(50), [TO VENDOR] VARCHAR(50), [PACKAGE TYPE] VARCHAR(50),
			 [REQUEST TYPE] VARCHAR(50), [ASSM REVISION NO] VARCHAR(50),[TEST LOCATION] VARCHAR(50),
			 [STATUS] VARCHAR(50),[SHIP TO VENDOR] VARCHAR(50), [PIN COUNT] VARCHAR(50),
			 [REEL QTY] INT, [TURN KEY] VARCHAR(50), [INSTRUCTIONS] VARCHAR(500), [LOT NUMBER] VARCHAR(200),
			 [FAB LOT NUMBER] VARCHAR(200),[WAFER QTY] INT,[WAFER ID] VARCHAR(1000),[QTY PULLED] INT,
			 [BIN INFO] VARCHAR(50),[PART NUMBER] VARCHAR(50),[WORK WEEK] VARCHAR(10))

	IF @REPORTTYPE = 'XLREPORT' AND @ISDETAIL = 'DETAIL'
	BEGIN
		SET @SQLSTRING = N' INSERT INTO #RELEASE_TABLE ( [ASSM RELEASE NO], [WORK WEEK], [TO VENDOR],[PART NUMBER], [PIN COUNT], [PACKAGE TYPE], [REQUEST TYPE], [LOT NUMBER], [FAB LOT NUMBER], [QTY PULLED], [RELEASE DATE], [STATUS]  )
		SELECT ASSEMBLY_RELEASE_NO, NULL, ( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = TO_VENDOR_ID ),P.PART_NUMBER, PIN_COUNT, PACKAGE_TYPE,REQUEST_TYPE,
		ARLT.Shipped_Lot_Number, Lt.Fab_Lot_Number, ARLT.Qty_Pulled, ASSEMBLY_RELEASE_DATE, STATUS FROM
		ASSM_release_TAble ART, Assm_release_lot_Table ARLT, Lot_Table Lt, Product P
		WHERE ART.Assembly_release_ID = ARLT.Assembly_release_ID AND ARLT.Lot_ID = Lt.Lot_ID and ART.ProcessStep_ID = P.ProcessStep_ID'
	END
	ELSE
	BEGIN
		SET @SQLSTRING = N' INSERT INTO #RELEASE_TABLE
		SELECT 
			 ART.ASSEMBLY_RELEASE_ID,ASSEMBLY_RELEASE_NO,min(ASSEMBLY_RELEASE_DATE),PO_NUMBER,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = FROM_VENDOR_ID ),
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = TO_VENDOR_ID ),
			PACKAGE_TYPE,REQUEST_TYPE,ASSM_REVESION_NUMBER,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = TEST_LOCATION_ID ),STATUS,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = SHIP_TO_VENDOR_ID ),PIN_COUNT,
			( SELECT TOP 1 REEL_QTY FROM REEL_QTY_TABLE WHERE PACKAGE= PACKAGE_TYPE AND PIN_COUNT= PIN_COUNT ),
			(CASE 
				WHEN ( TURN_KEY = 0 AND IS_TEST = 0 AND IS_TAPE_REEL = 0 ) THEN ''ASSM+TEST+T&R''
				WHEN ( TURN_KEY = 0 AND IS_TEST = 0 AND IS_TAPE_REEL = 1 ) THEN ''ASSM+TEST''
				ELSE ''ASSEMBLY''
			END),
			null,NULL,null,
			count(WAFER_ID),null,sum(QTY_PULLED),ARLT.BIN_INFO,P.PART_NUMBER, NULL
		FROM 
			ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT,PRODUCT P
		WHERE 
			ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
			AND ART.PROCESSSTEP_ID = P.PROCESSSTEP_ID '
	END


	IF @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ART.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @PROCESSSTEP_IDS+''') )'
	END

	IF @VENDORID IS NOT NULL AND @VENDORID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND TO_VENDOR_ID = '''+ convert(varchar,@VENDORID)+ ''''
	END

	IF @STATUS IS NOT NULL AND @STATUS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND STATUS = '''+ @STATUS+ ''''
	END

	IF @CATEGORY IS NOT NULL AND @CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND REQUEST_TYPE = '''+ @CATEGORY+ ''''
	END

	IF @FROMDATE IS NOT NULL AND @FROMDATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' AND CONVERT(DATETIME,ASSEMBLY_RELEASE_DATE) >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF @TODATE IS NOT NULL AND @TODATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' AND CONVERT(DATETIME,ASSEMBLY_RELEASE_DATE) <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END

	IF @REPORTTYPE = 'XLREPORT' AND @ISDETAIL = 'DETAIL'
	BEGIN
		SET @SQLSTRING = @SQLSTRING
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' GROUP BY 
					ART.ASSEMBLY_RELEASE_ID,ASSEMBLY_RELEASE_NO,PO_NUMBER,FROM_VENDOR_ID,TO_VENDOR_ID,
					PACKAGE_TYPE,REQUEST_TYPE,ASSM_REVESION_NUMBER,TEST_LOCATION_ID,STATUS,SHIP_TO_VENDOR_ID,
					PIN_COUNT,TURN_KEY,IS_TEST,IS_TAPE_REEL,ARLT.BIN_INFO,P.PART_NUMBER '

	END
	--print'@SQLSTRING :-'+@SQLSTRING
	EXEC (@SQLSTRING)

	IF @REPORTTYPE = 'XLREPORT' AND @ISDETAIL = 'DETAIL'
	BEGIN
		SET @RELEASE_CURSOR = CURSOR FOR SELECT DISTINCT [ASSM REL ID],[RELEASE DATE] FROM #RELEASE_TABLE
		OPEN @RELEASE_CURSOR
		FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @RELEASE_DATE
		WHILE @@FETCH_STATUS = 0
		BEGIN
				
				SELECT  @@WW_NO = WW_Number from FN_CALCULATE_WORK_WEEK ( @RELEASE_DATE )

				SET @LOT_CURSOR = CURSOR FOR SELECT DISTINCT LOT_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID

				OPEN @LOT_CURSOR
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
				WHILE @@FETCH_STATUS = 0
				BEGIN
					DELETE FROM @WAFER_IDS_TABLE
					
					SET @WAFER_IDS = ''	
		
					INSERT INTO @WAFER_IDS_TABLE 
					SELECT LOT_ID,WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID

					SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
					WHILE ( @@ROWCOUNT > 0 ) 
					BEGIN
						IF ( @WAFER_IDS = '' )
						BEGIN
							SET @WAFER_IDS = @WAFER_ID
						END
						ELSE
						BEGIN 
							SET @WAFER_IDS = @WAFER_IDS +','+ @WAFER_ID
						END
						
						DELETE FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID  
												
						SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
					END

					SELECT @LOT_NUMBER = LOT_NUMBER, @FAB_LOT_NUMBER = FAB_LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
					
					IF @FAB_LOT_NUMBERS IS NULL
					BEGIN
						--SET @LOT_NUMBERS = @LOT_NUMBER
						SET @FAB_LOT_NUMBERS = @FAB_LOT_NUMBER
						SET @LOTWAFER_IDS = @WAFER_IDS
					END
					ELSE
					BEGIN
						--SET @LOT_NUMBERS = @LOT_NUMBERS + '/'+@LOT_NUMBER
						SET @FAB_LOT_NUMBERS = @FAB_LOT_NUMBERS + '/'+@FAB_LOT_NUMBER
						SET @LOTWAFER_IDS = @LOTWAFER_IDS +'/'+@WAFER_IDS
					END
					
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
				END
				CLOSE @LOT_CURSOR
				DEALLOCATE @LOT_CURSOR
	
			UPDATE #RELEASE_TABLE SET [WAFER ID] = @LOTWAFER_IDS, [WORK WEEK] = @@WW_NO WHERE [ASSM REL ID] = @RELEASE_ID
				
			FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @RELEASE_DATE

			SET @WAFER_IDS = NULL
			SET @LOT_NUMBERS = NULL
			SET @LOTWAFER_IDS = NULL
			SET @DB_INSTRUCTIONS = NULL

			DELETE FROM @WAFER_IDS_TABLE
		END
		CLOSE @RELEASE_CURSOR
		DEALLOCATE @RELEASE_CURSOR		
	END
	ELSE
	BEGIN
		SET @RELEASE_CURSOR = CURSOR FOR SELECT [ASSM REL ID], [RELEASE DATE] FROM #RELEASE_TABLE
		OPEN @RELEASE_CURSOR
		FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @RELEASE_DATE
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @DB_INSTRUCTIONS = DB_INSTRUCTIONS FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
			SELECT  @@WW_NO = WW_Number from FN_CALCULATE_WORK_WEEK ( @RELEASE_DATE )
			IF @ISDETAIL = 'DETAIL'
			BEGIN
				SET @LOT_CURSOR = CURSOR FOR SELECT DISTINCT LOT_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID

				OPEN @LOT_CURSOR
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
				WHILE @@FETCH_STATUS = 0
				BEGIN
					DELETE FROM @WAFER_IDS_TABLE
					
					SET @WAFER_IDS = ''	
		
					INSERT INTO @WAFER_IDS_TABLE 
					SELECT LOT_ID,WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID

					SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
					WHILE ( @@ROWCOUNT > 0 ) 
					BEGIN
						IF ( @WAFER_IDS = '' )
						BEGIN
							SET @WAFER_IDS = @WAFER_ID
						END
						ELSE
						BEGIN 
							SET @WAFER_IDS = @WAFER_IDS +','+ @WAFER_ID
						END
						
						DELETE FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID  
												
						SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
					END

					SELECT @LOT_NUMBER = LOT_NUMBER, @FAB_LOT_NUMBER = FAB_LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
					
					IF @FAB_LOT_NUMBERS IS NULL
					BEGIN
						--SET @LOT_NUMBERS = @LOT_NUMBER
						SET @FAB_LOT_NUMBERS = @FAB_LOT_NUMBER
						SET @LOTWAFER_IDS = @WAFER_IDS
					END
					ELSE
					BEGIN
						--SET @LOT_NUMBERS = @LOT_NUMBERS + '/'+@LOT_NUMBER
						SET @FAB_LOT_NUMBERS = @FAB_LOT_NUMBERS + '/'+@FAB_LOT_NUMBER
						SET @LOTWAFER_IDS = @LOTWAFER_IDS +'/'+@WAFER_IDS
					END
					
				FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
				END
				CLOSE @LOT_CURSOR
				DEALLOCATE @LOT_CURSOR

				SELECT @LOT_NUMBERS = COALESCE(@LOT_NUMBERS + '/', '') + 
				Shipped_lot.Shipped_lot_Number 
				FROM (SELECT DISTINCT Shipped_lot_Number FROM Assm_release_lot_Table where assembly_release_ID = @RELEASE_ID ) Shipped_lot

				UPDATE #RELEASE_TABLE 
					SET [WAFER ID] = @LOTWAFER_IDS, [FAB LOT NUMBER] = @FAB_LOT_NUMBERS,[LOT NUMBER] = @LOT_NUMBERS,
						[INSTRUCTIONS] = @DB_INSTRUCTIONS, [WORK WEEK] = @@WW_NO
				WHERE 
					[ASSM REL ID] = @RELEASE_ID
			END
			ELSE
			BEGIN
				UPDATE #RELEASE_TABLE 
					SET [INSTRUCTIONS] = @DB_INSTRUCTIONS
				WHERE 
					[ASSM REL ID] = @RELEASE_ID
			END

			SET @WAFER_IDS = NULL
			SET @LOT_NUMBERS = NULL
			SET @LOTWAFER_IDS = NULL
			SET @DB_INSTRUCTIONS = NULL

			DELETE FROM @WAFER_IDS_TABLE

		FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @RELEASE_DATE
		END
		CLOSE @RELEASE_CURSOR
		DEALLOCATE @RELEASE_CURSOR
	END
	

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN	
		IF @ISDETAIL = 'DETAIL'
		BEGIN
			SELECT 
				 [ASSM RELEASE NO],[WORK WEEK],[TO VENDOR],[PART NUMBER],[PIN COUNT],[PACKAGE TYPE],[REQUEST TYPE],[LOT NUMBER] AS [Shipped Lot Number],[FAB LOT NUMBER],[WAFER ID],SUM([QTY PULLED]) AS [RELEASED QTY] , [RELEASE DATE],[STATUS]
			FROM 
				#RELEASE_TABLE Group By [ASSM RELEASE NO],[WORK WEEK],[TO VENDOR],[PART NUMBER],[PIN COUNT],[PACKAGE TYPE],[REQUEST TYPE],[LOT NUMBER],[FAB LOT NUMBER],[WAFER ID],[RELEASE DATE],[STATUS]
			ORDER BY [RELEASE DATE],[ASSM RELEASE NO]  DESC
		END
		ELSE
		BEGIN
			SELECT 
				 [ASSM RELEASE NO], [RELEASE DATE], [PART NUMBER], [FROM VENDOR],[TO VENDOR],
				 [QTY PULLED],[PACKAGE TYPE],[PO NO],[REQUEST TYPE],[ASSM REVISION NO],[TEST LOCATION],[STATUS],
				 [SHIP TO VENDOR],[PIN COUNT],[REEL QTY],[TURN KEY],[INSTRUCTIONS],[BIN INFO]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE],[ASSM RELEASE NO] DESC
		END
	END
	ELSE
	BEGIN
		IF @ISDETAIL = 'DETAIL'
		BEGIN
			SELECT 
				 [ASSM REL ID],[ASSM RELEASE NO], [RELEASE DATE], [PO NO],[FROM VENDOR],[TO VENDOR],
				 [PACKAGE TYPE],[REQUEST TYPE],[ASSM REVISION NO],[TEST LOCATION],[STATUS],
				 [SHIP TO VENDOR],[PIN COUNT],[REEL QTY],[TURN KEY],[INSTRUCTIONS],[FAB LOT NUMBER],
				 [WAFER QTY],[WAFER ID],[QTY PULLED],[BIN INFO],[PART NUMBER]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE] DESC
		END
		ELSE
		BEGIN
			SELECT 
				 [ASSM REL ID],[ASSM RELEASE NO], [RELEASE DATE], [PO NO],[FROM VENDOR],[TO VENDOR],
				 [PACKAGE TYPE],[REQUEST TYPE],[ASSM REVISION NO],[TEST LOCATION],[STATUS],
				 [SHIP TO VENDOR],[PIN COUNT],[REEL QTY],[TURN KEY],[INSTRUCTIONS],
				 [QTY PULLED],[BIN INFO],[PART NUMBER]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE] DESC
		END
	END

END
